package org.vy.dal.dao.lookup;

import java.util.List;

import org.vy.dal.QueryManager;

public class ViewVyLookupCitiesDAO {

	private static final String FIND_BY_PK = "SELECT * FROM <TABLE> WHERE CITY_CODE = m_CityCode";
	private static final String FIND_BY_COUNTRY_CODE = "SELECT * FROM <TABLE> WHERE COUNTRY_CODE = m_CountryCode";
	private static final String FIND_UNIQUE_COUNTRIES = "SELECT DISTINCT COUNTRY_CODE, COUNTRY_NAME, COUNTRY_HINDI_NAME FROM <TABLE> ORDER BY COUNTRY_NAME ASC";
	private static final String FIND_STATE_BY_COUNTRY = "SELECT DISTINCT STATE_CODE, STATE_NAME, STATE_HINDI_NAME FROM <TABLE> WHERE COUNTRY_CODE LIKE m_CountryCode ORDER BY STATE_NAME ASC";
	private static final String FIND_CITIES_BY_STATE_AND_COUNTRY = "SELECT DISTINCT CITY_CODE, CITY_NAME, CITY_HINDI_NAME FROM <TABLE> WHERE COUNTRY_CODE = m_CountryCode AND STATE_CODE = m_StateCode";
	private static final String FIND_BY_PARAM = "SELECT * FROM <TABLE> WHERE 1 = 1 ";
	private static final String UPDATE = "UPDATE <TABLE> SET CITY_NAME=m_CityName,CITY_HINDI_NAME=m_CityHindiName,STATE_CODE=m_StateCode,STATE_NAME=m_StateName,STATE_HINDI_NAME=m_StateHindiName,COUNTRY_CODE=m_CountryCode,COUNTRY_NAME=m_CountryName, COUNTRY_HINDI_NAME=m_CountryHindiName WHERE CITY_CODE = m_CityCode";
	private static final String INSERT = "INSERT INTO <TABLE> (CITY_CODE,CITY_NAME,CITY_HINDI_NAME,STATE_CODE,STATE_NAME,STATE_HINDI_NAME,COUNTRY_CODE,COUNTRY_NAME,COUNTRY_HINDI_NAME) VALUES (m_CityCode,m_CityName,m_CityHindiName,m_StateCode,m_StateName,m_StateHindiName,m_CountryCode,m_CountryName,m_CountryHindiName)";
	private static final String DELETE = "DELETE FROM <TABLE> WHERE CITY_CODE = m_CityCode";

	public static ViewVyLookupCitiesDO findByPK(String key) throws Exception {
		ViewVyLookupCitiesDO dto = new ViewVyLookupCitiesDO();
		dto.setCityCode(key);
		dto = (ViewVyLookupCitiesDO) QueryManager.executeQuery(dto, FIND_BY_PK);
		return dto;
	}

	public static ViewVyLookupCitiesDO findByCountryCode(String code) throws Exception {
		ViewVyLookupCitiesDO dto = new ViewVyLookupCitiesDO();
		dto.setCountryCode(code);
		dto = (ViewVyLookupCitiesDO) QueryManager.executeQuery(dto, FIND_BY_COUNTRY_CODE);
		return dto;
	}

	public static List<ViewVyLookupCitiesDO> findUniqueCountries() throws Exception {
		ViewVyLookupCitiesDO dto = new ViewVyLookupCitiesDO();
		return (List<ViewVyLookupCitiesDO>) QueryManager.executeListQuery(dto, FIND_UNIQUE_COUNTRIES);
	}

	public static List<ViewVyLookupCitiesDO> findStateByCountry(String countryCode) throws Exception {
		ViewVyLookupCitiesDO dto = new ViewVyLookupCitiesDO();
		dto.setCountryCode(countryCode);
		return (List<ViewVyLookupCitiesDO>) QueryManager.executeListQuery(dto, FIND_STATE_BY_COUNTRY);
	}

	public static List<ViewVyLookupCitiesDO> findCitiesByStateAndCountry(String stateCode, String countryCode) throws Exception {
		ViewVyLookupCitiesDO dto = new ViewVyLookupCitiesDO();
		dto.setCountryCode(countryCode);
		dto.setStateCode(stateCode);
		return (List<ViewVyLookupCitiesDO>) QueryManager.executeListQuery(dto, FIND_CITIES_BY_STATE_AND_COUNTRY);
	}
	
	public static ViewVyLookupCitiesDO delete(String key) throws Exception {
		ViewVyLookupCitiesDO dto = new ViewVyLookupCitiesDO();
		dto.setCityCode(key);
		boolean i = QueryManager.executeDelete(dto, DELETE);
		if (i == true)
			return dto;
		else
			return null;
	}

	public static ViewVyLookupCitiesDO findByParam(ViewVyLookupCitiesDO dto) throws Exception {
		StringBuilder sb = new StringBuilder(FIND_BY_PARAM);
		if(dto.getCityCode() != null){
			sb.append(" AND CITY_CODE LIKE '%"+ dto.getCityCode() +"%' ");
		}
		if(dto.getCityName() != null){
			sb.append(" AND CITY_NAME LIKE '%"+ dto.getCityName() +"%' ");
		}
		if(dto.getStateCode() != null){
			sb.append(" AND STATE_CODE LIKE '%"+ dto.getStateCode() +"%' ");
		}
		if(dto.getStateName() != null){
			sb.append(" AND STATE_NAME LIKE '%"+ dto.getStateName() +"%' ");
		}
		if(dto.getCountryCode() != null){
			sb.append(" AND COUNTRY_CODE LIKE '%"+ dto.getCountryCode() +"%' ");
		}
		if(dto.getCountryName() != null){
			sb.append(" AND COUNTRY_NAME LIKE '%"+ dto.getCountryName() +"%' ");
		}
		System.out.println(sb.toString());
		dto = (ViewVyLookupCitiesDO) QueryManager.executeQuery(dto, FIND_BY_PK);
		return dto;
	}

	public static ViewVyLookupCitiesDO insert(ViewVyLookupCitiesDO dto)
			throws Exception {

		dto = (ViewVyLookupCitiesDO) QueryManager.executeInsert(dto, INSERT);
		return dto;
	}

	public static ViewVyLookupCitiesDO update(ViewVyLookupCitiesDO dto)
			throws Exception {
		int i = QueryManager.executeUpdate(dto, UPDATE);
		if (i > 0)
			return dto;
		else
			return null;
	}
	@SuppressWarnings("unchecked")
	public static List<ViewVyLookupCitiesDO> showCitiesList(ViewVyLookupCitiesDO dto) throws Exception {

		StringBuilder sb = new StringBuilder(FIND_BY_PARAM);
		if (dto.getCityCode() != null) {
			sb.append(" AND CITY_CODE LIKE '%" + dto.getCityCode() + "%' ");
		}
		if (dto.getCityName() != null) {
			sb.append(" AND CITY_NAME LIKE '%" + dto.getCityName() + "%' ");
		}
		if (dto.getStateCode() != null) {
			sb.append(" AND STATE_CODE LIKE '%" + dto.getStateCode() + "%' ");
		}
		if (dto.getStateName() != null) {
			sb.append(" AND STATE_NAME LIKE '%" + dto.getStateName() + "%' ");
		}
		if (dto.getCountryCode() != null) {
			sb.append(" AND COUNTRY_CODE LIKE '%" + dto.getCountryCode() + "%' ");
		}
		if (dto.getCountryName() != null) {
			sb.append(" AND COUNTRY_NAME LIKE '%" + dto.getCountryName() + "%' ");
		}

		System.out.println(sb.toString());
		return (List<ViewVyLookupCitiesDO>) QueryManager.executeListQuery(dto, sb
				.toString());
	}
}
